package com.parkreport.report.service;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.xml.rpc.ServiceException;

import com.common.jdbc.DB4ObjUtil;
import com.common.jdbc.SqlUtil;
import com.common.report.service.ReportService;
import com.common.report.vo.ReportCountDataVO;
import com.common.report.vo.ReportTitleVO;
import com.common.util.StringUtil;
import com.parkreport.vo.ReportParamVO;

public class DataAnalysisReportService extends ReportService {

	private long carTicket = 1L;
	private String parkAreaIds = "";
	private int parkCount=0;
	@Override
	public List findDatas(HttpServletRequest req)  {
		startDate = req.getParameter("startDate");
		endDate = req.getParameter("endDate");
		parkAreaIds = req.getParameter("parkAreaIds");
	
		StringBuilder sql = new StringBuilder("");
		sql.append("select ca.cityname as ments,count(*) as counts,'总数' as titles  from");
		sql.append(" 	(select lpr.PLATENO,count(*) from");
		sql.append(" 		(select l1.* from LPR_RECORD l1,");
		sql.append("			(select max(id) as id  from LPR_RECORD where 1=1 ");
		if(!StringUtil.isBlank(startDate)){
			sql.append(" and RQSJ>='"+startDate+"'");
		}
		if(!StringUtil.isBlank(endDate)){
			sql.append(" and RQSJ<='"+endDate+"'");
		}
		sql.append(" 			group by PLATENO,nid) l2 where l2.id=l1.id) lpr,");
		sql.append("		QUERY_DMB qd,PARK_DMB pd,PARK_AREA pa");
		sql.append(" 		where 1=1 and pd.TYPE='1' and lpr.NID = qd.NID  and qd.NID = pd.NID and pd.PARK_AREA_ID=pa.ID");
		if(!StringUtil.isBlank(parkAreaIds)){
			String[] parkArr = parkAreaIds.split(",");
			parkCount = parkArr.length;
			sql.append(SqlUtil.getInSql("pa.ID", parkAreaIds));
		}
		sql.append(" 		group by lpr.PLATENO having count(*)>="+parkCount+") lpr1,CAR_AREA ca where 1=1  and substr(lpr1.PLATENO,1,2) =ca.carNum");
		sql.append(" group by ca.cityname\n");
		sql.append(" order by counts desc");
		/*sql.append("select count(*) as counts,ca.CityName as ments,'总数' as  titles");
		sql.append(" from (select lpr.PLATENO,count(distinct nid) from lpr_record lpr where nid in(1,2)");
		if(!StringUtil.isBlank(startDate)){
			sql.append(" and lpr.RQSJ>='"+startDate+"'");
		}
		if(!StringUtil.isBlank(endDate)){
			sql.append(" and lpr.RQSJ<='"+endDate+"'");
		}
		sql.append(" group by PLATENO having count(distinct nid)>=2) l, CAR_AREA ca");
		sql.append(" where 1=1 and substr(l.PLATENO,1,2) =ca.carNum");
		
		sql.append(" group by ca.CityName");
		sql.append(" order by counts desc");*/
		log.info("sql==========\n"+sql);
		List<ReportCountDataVO> list = DB4ObjUtil.execute4Obj(new ReportCountDataVO(), sql.toString());
		list.addAll(findtotal());
		//list.addAll(findsubtotal());
		//list.addAll(findalltotal());
		return list;
	}
	
	public List<ReportCountDataVO> findtotal() {
		StringBuilder sql = new StringBuilder("");
		sql.append("select count(*) as counts,'合计' as ments,'总数' as  titles from\n");
		sql.append(" 	(select lpr.PLATENO,count(*) from\n");
		sql.append(" 		(select l1.* from LPR_RECORD l1,\n");
		sql.append("			(select max(id) as id  from LPR_RECORD where 1=1 ");
		if(!StringUtil.isBlank(startDate)){
			sql.append(" and RQSJ>='"+startDate+"'");
		}
		if(!StringUtil.isBlank(endDate)){
			sql.append(" and RQSJ<='"+endDate+"'");
		}
		sql.append(" group by PLATENO,nid) l2 where l2.id=l1.id) lpr,\n");
		sql.append("		QUERY_DMB qd,PARK_DMB pd,PARK_AREA pa\n");
		sql.append(" 		where 1=1 and pd.TYPE='1' and lpr.NID = qd.NID  and qd.NID = pd.NID and pd.PARK_AREA_ID=pa.ID\n");
		if(!StringUtil.isBlank(parkAreaIds)){
			sql.append(SqlUtil.getInSql("pa.ID", parkAreaIds));
		}
		sql.append(" 		group by lpr.PLATENO having count(*)>="+parkCount+") lpr1,CAR_AREA ca where 1=1  and substr(lpr1.PLATENO,1,2) =ca.carNum\n");
		/*if(!StringUtil.isBlank(startDate)){
			sql.append(" and lpr.RQSJ>='"+startDate+"'");
		}
		if(!StringUtil.isBlank(endDate)){
			sql.append(" and lpr.RQSJ<='"+endDate+"'");
		}
		sql.append(" group by PLATENO having count(distinct nid)>=2) l, CAR_AREA ca");
		sql.append(" where 1=1 and substr(l.PLATENO,1,2) =ca.carNum");
		*/
		//sql.append(" group by ca.CityName");
		
		log.info("sql==========\n"+sql);
		List<ReportCountDataVO> list = DB4ObjUtil.execute4Obj(new ReportCountDataVO(), sql.toString());
		
		
		return list;
	}
	
	
	@Override
	public List<ReportTitleVO> findTitles(HttpServletRequest req)  {
		List<ReportTitleVO> list = new ArrayList<ReportTitleVO>();
		list.add(new ReportTitleVO("总数","总数"));
		return list;
	}
	
	@Override
	public List<ReportTitleVO> findMents(HttpServletRequest req)  {
		StringBuilder sql = new StringBuilder("");
		sql.append("select CityName as code,CityName as name from CAR_AREA group by CityName");
		List<ReportTitleVO> list = DB4ObjUtil.execute4Obj(new ReportTitleVO(), sql.toString());
		list.add(new ReportTitleVO("合   计","合计"));
		return list;
	}
}
